package gruppo12;



import java.beans.Statement;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Stefano
 */
public class MetodiAnagrafica{
        
    
        
 
	public  Boolean InserimentoAnagrafica(String Nome,String Indirizzo,String Piva,String Tel1,String Tel2,String Tel3,String Email1,String Email2,int tipo) throws SQLException {
 
		Connection dbConnection = null;
		PreparedStatement preparedStatementInsert = null;
             
 
		String insertTableSQL = "INSERT INTO test.elencoanagrafica "
				+ "(nome,indirizzo,piva,tel1,tel2,tel3,email1,email2,tipo) VALUES "
				+ "(?,?,?,?,?,?,?,?,?)";
 
		
 
 
		try {   DbConnection db =new DbConnection();
			dbConnection =db.getDBConnection();
 
			dbConnection.setAutoCommit(false);
 
			preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
			preparedStatementInsert.setString(1, Nome.toUpperCase());
			preparedStatementInsert.setString(2, Indirizzo.toUpperCase());
			preparedStatementInsert.setString(3, Piva.toUpperCase());
                        preparedStatementInsert.setString(4, Tel1.toUpperCase());
                        preparedStatementInsert.setString(5, Tel2.toUpperCase());
                        preparedStatementInsert.setString(6, Tel3.toUpperCase());
                        preparedStatementInsert.setString(7, Email1);
                        preparedStatementInsert.setString(8, Email2);
                        preparedStatementInsert.setInt(9, tipo);
                        
                       if(db.controllo (Piva,"Piva","test.elencoanagrafica"));
		
                  
                        int rs = preparedStatementInsert.executeUpdate();
			
 
			
 
			dbConnection.commit();
 
			if (rs == 1 ){
                            System.out.println("Done!");
                            return true;
                        }
                         
                        return false;
		        
                
                } catch (SQLException e) {
 
			System.out.println(e.getMessage());
			dbConnection.rollback();
                        return false;
 
		} finally {
 
			if (preparedStatementInsert != null) {
				preparedStatementInsert.close();
			}
 
			
 
			if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
 
	}
 //rilascia i contenuti della tabella del tipo di anagrafica voluto
	public  String [][] VisualizzaAnagrafica(int tipo) throws SQLException {
 
		Connection dbConnection = null;
		java.sql.Statement statement = null;
             
 
		String insertTableSQL = "SELECT nome,indirizzo,piva,tel1,tel2,tel3,email1,email2 "
				+ "FROM test.elencoanagrafica where tipo="+Integer.toString(tipo);
                                  
			
 
		
 
 
		try {
			dbConnection = new DbConnection().getDBConnection();
 
			dbConnection.setAutoCommit(false);
 
			statement = dbConnection.createStatement(1004, 1008);
		
                        
                   
		 
                    
                       java.sql.ResultSet rs = statement.executeQuery(insertTableSQL);
		      
                       String[][] results=null;
                       
                       int rows=0;
                       while(rs.next())//scorre all'ultimo record del select
                       {rows++;}
                       results= new String[rows][];    
                       rs.beforeFirst();                      //ritorna il cursore all'inizio
                       int i=0;
                       while (rs.next()){
                           String[] record = new String[8];
                           record[0]=rs.getString(1);
                           record[1]=rs.getString(2);
                           record[2]=rs.getString(3);
                           record[3]=rs.getString(4);
                           record[4]=rs.getString(5);
                           record[5]=rs.getString(6);
                           record[6]=rs.getString(7);
                           record[7]=rs.getString(8);
                           results[i]=record ;
                           i++;
                       }
                       int j;
                       	for(int k=1;k<results.length;k++){
                            String[] temp=results[k];
                            j=k-1;
                            while((j>=0)&&(results[j][0].charAt(0) >temp[0].charAt(0)))
                                	{
				results[j+1]=results[j];
				j--;
                            
			}
                            results[j+1]=temp;
                        }		
                       
			dbConnection.commit();
 
			return results;
		        
                
                } catch (SQLException e) {
 
			System.out.println(e.getMessage());
			dbConnection.rollback();
                        return null;
 
		} finally {
 
			
 
			
 
			if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
 
	}
 
        public Boolean CancellaAnagrafica(String key) throws SQLException{
                Connection dbConnection = null;
		PreparedStatement preparedStatementInsert = null;
             
 
		String query = "DELETE "
				+ "FROM test.elencoanagrafica where piva = (?)";
     
     
     try {
			dbConnection = new DbConnection().getDBConnection();
 
			dbConnection.setAutoCommit(false);
                        preparedStatementInsert = dbConnection.prepareStatement(query);
			preparedStatementInsert.setString(1, key);
                        
                     
		
                  
                        int rs = preparedStatementInsert.executeUpdate();
			
 
			
 
			dbConnection.commit();
 
			if (rs == 1 ){
                            System.out.println("Done!");
                            return true;
                        }
                         
                        return false;
		        
                
                } catch (SQLException e) {
 
			System.out.println(e.getMessage());
			dbConnection.rollback();
                        return false;
 
		} finally {
 
			if (preparedStatementInsert != null) {
				preparedStatementInsert.close();
			}
 
			
 
			if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
 }
        
        public  String [][] CercaAnagrafica(String filter,String colonna,int tipo) throws SQLException {
 
		Connection dbConnection = null;
		java.sql.Statement statement = null;
               String insertTableSQL = "SELECT * "
				+ "FROM test.elencoanagrafica WHERE "+colonna+" LIKE '"+ filter.toUpperCase()+"%' AND tipo="+tipo;                          
			
 
		
 
 
		try {
			dbConnection = new DbConnection().getDBConnection();
 
			dbConnection.setAutoCommit(false);
 
			statement = dbConnection.createStatement(1004, 1008);
		
                        
                   
		 
                    
                       java.sql.ResultSet rs = statement.executeQuery(insertTableSQL);
		      
                       String[][] results=null;
                       
                       int rows=0;
                       while(rs.next())//scorre all'ultimo record del select
                       {rows++;}
                       results= new String[rows][];    
                       rs.beforeFirst();                      //ritorna il cursore all'inizio
                       int i=0;
                       while (rs.next()){
                           String[] record = new String[8];
                           record[0]=rs.getString(1);
                           record[1]=rs.getString(2);
                           record[2]=rs.getString(3);
                           record[3]=rs.getString(4);
                           record[4]=rs.getString(5);
                           record[5]=rs.getString(6);
                           record[6]=rs.getString(7);
                           record[7]=rs.getString(8);
                           results[i]=record ;
                           //ordine alfabetico con insertion sort
                           
                               
                           i++;
                       }
                int j;
                       	for(int k=1;k<results.length;k++){
                            String[] temp=results[k];
                            j=k-1;
                            while((j>=0)&&(results[j][0].charAt(0) >temp[0].charAt(0)))
                                	{
				results[j+1]=results[j];
				j--;
                            
			}
                            results[j+1]=temp;
                        }	
 
			
 
			dbConnection.commit();
 
			return results;
		        
                
                } catch (SQLException e) {
 
			System.out.println(e.getMessage());
			dbConnection.rollback();
                        return null;
 
		} finally {
 
			
 
			
 
			if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
 
	}

        
    
        
        public  void Insertion_sort(String[][] A)
	{
		int k,j;
		for(int i=1;i<A.length;i++)
		{
			String[] temp=A[i];
			j=i-1;
			while((j>=0)&&(A[j][0].compareTo(temp[0])>0))
			{
				A[j+1]=A[j];
				j--;
			}
			A[j+1]=temp;
		}
	}
 



}

